/*
 * Copyright (C) 2003, Velocityme Partnership
 * SearchSessionBean.java
 *
 * Created on 21 July 2003, 09:07
 */

package com.velocityme.session;

import com.velocityme.entity.ChangeDeltaTypeBean;
import com.velocityme.entity.PermissionBean;
import javax.ejb.*;
import javax.naming.*;
import java.util.*;

import com.velocityme.interfaces.*;
import com.velocityme.utility.ChangeDeltaHelper;
import com.velocityme.utility.DataSourceConnection;
import com.velocityme.utility.InvalidKeyException;
import com.velocityme.utility.NodeSearchResult;
import com.velocityme.utility.PermissionCache;
import com.velocityme.utility.SearchEntry;
import com.velocityme.valueobjects.*;
import java.io.ByteArrayOutputStream;
import java.rmi.RemoteException;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.sql.DataSource;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.Result;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.apache.log4j.Logger;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

/**
 *
 * @author  Robert Crida Work
 * @ejb.bean
 *           type="Stateless"
 *           cmp-version="2.x"
 *           name="SearchSession"
 *           jndi-name="ejb/SearchSession"
 *           view-type="local"
 *           transaction-type="Container"
 * @ejb.transaction type="Required"
 *
 * @ejb.util generate="physical"
 */
public class SearchSessionBean implements SessionBean {
    
    private SessionContext m_context;

    private ChangeDeltaHelper m_cdHelper;
    
    private NodeLocal m_directoryNodeLocal;
    
    private PermissionLocal m_permissionNodeView;
    
    private PermissionCache m_permissionCache;

    /**
     * Search the node tree for the complete set of nodes forming the branch
     * below the specified node.
     *
     * @param p_key User's security key
     * @param p_node The node which is the root of the search
     * @param p_down Is the search recursive or not
     *
     * @ejb.interface-method view-type="local"
     **/
    public SearchLocal createSearchSpaceLocal(com.velocityme.interfaces.KeySessionLocal p_keyLocal,
                                              com.velocityme.interfaces.NodeLocal p_nodeLocal,
                                              java.lang.Boolean p_down)
        throws InvalidKeyException {
        return createSearchSpaceFilteredLocal(p_keyLocal, p_nodeLocal, new ArrayList(), p_down);
    }
    
    /**
     * Search the node tree for the complete set of nodes forming the branch
     * below the specified node but excluding any in the exclusion set.
     *
     * @param p_key User's security key
     * @param p_node The node which is the root of the search
     * @param p_down Is the search recursive or not
     *
     * @ejb.interface-method view-type="local"
     **/
    public SearchLocal createSearchSpaceFilteredLocal(com.velocityme.interfaces.KeySessionLocal p_keyLocal,
                                                      com.velocityme.interfaces.NodeLocal p_nodeLocal,
                                                      java.util.Collection p_exclusionNodesLocal,
                                                      java.lang.Boolean p_down)
        throws InvalidKeyException {
        try {
            if (p_keyLocal.isValid()) {
                int level = 0;
                SearchLocal searchLocal = SearchUtil.getLocalHome().create();
                Collection nodesLocal = new ArrayList();
                nodesLocal.add(p_nodeLocal);
                if (p_down.booleanValue() == true) {
                    // add all nodes at each level below the current one
                    SearchLevelLocal searchLevelLocal = SearchLevelUtil.getLocalHome().create(searchLocal, new Integer(level++), nodesLocal);
                    do {
                        nodesLocal = NodeUtil.getLocalHome().findSubLevel(searchLevelLocal);
                        nodesLocal.removeAll(p_exclusionNodesLocal);
                        searchLevelLocal = SearchLevelUtil.getLocalHome().create(searchLocal, new Integer(level++), nodesLocal);
                    } while(!nodesLocal.isEmpty());
                }
                else {
                    // add the parent node of the current one, in only one level
                    NodeLocal parentLocal = p_nodeLocal.getParentNodeLocal();
                    while (parentLocal != null) {
                        nodesLocal.add(parentLocal);
                        parentLocal = parentLocal.getParentNodeLocal();
                    }
                    SearchLevelUtil.getLocalHome().create(searchLocal, new Integer(0), nodesLocal);
                }
                return searchLocal;
            }
            else
                throw new InvalidKeyException();
        }
        catch(NamingException e) {
            throw new EJBException(e);
        }
        catch(CreateException e) {
            throw new EJBException(e);
        }
        catch(FinderException e) {
            throw new EJBException(e);
        }
    }
    
    /**
     * Remove the specified node search space from the database when it is no
     * longer required.
     *
     * @param p_key User's security key
     * @param p_search The search space to be removed
     *
     * @ejb.interface-method view-type="local"
     **/
    public void removeSearchSpaceLocal(com.velocityme.interfaces.KeySessionLocal p_keyLocal, 
                                       com.velocityme.interfaces.SearchLocal p_searchLocal)
        throws InvalidKeyException {
        try {
            if (p_keyLocal.isValid()) {
                p_searchLocal.remove();
            }
            else
                throw new InvalidKeyException();
        }
        catch(RemoveException e) {
            throw new EJBException(e);
        }
    }
    
    /**
     * Get a set of all permissions that are currently assigned to the user in the
     * specified node
     *
     * @ejb.interface-method view-type="local"
     **/
    public java.util.Set getAssignedPermissionsLocal(com.velocityme.interfaces.KeySessionLocal p_keyLocal,
                                                     com.velocityme.interfaces.NodeLocal p_nodeLocal)
        throws InvalidKeyException {
        try {
            if (p_keyLocal.isValid()) {
                // return an empty set if the node is not specified
                if (p_nodeLocal == null)
                    return new HashSet();
                // return the set from the cache if not null
                Set permissionsLocal = m_permissionCache.get(p_keyLocal.getUserLocal().getUserId(), p_nodeLocal.getNodeId());
                if (permissionsLocal != null)
                    return permissionsLocal;
                // otherwise we need to do the work and look up the permissions
                SearchLocal searchLocal = createSearchSpaceLocal(p_keyLocal, p_nodeLocal, new Boolean(false));
                permissionsLocal = new HashSet(PermissionUtil.getLocalHome().findByUserAndSearch(p_keyLocal.getUserLocal(), searchLocal, p_nodeLocal));
                permissionsLocal.addAll(PermissionUtil.getLocalHome().findByGroupAndSearch(p_keyLocal.getUserLocal(), searchLocal, p_nodeLocal));
                removeSearchSpaceLocal(p_keyLocal, searchLocal);
                if (p_nodeLocal.equals(p_keyLocal.getUserLocal().getNodeLocal()) ||
                    p_nodeLocal.equals(p_keyLocal.getUserLocal().getPersonLocal().getContactableLocal().getNodeLocal()) ||
                    p_nodeLocal.equals(m_directoryNodeLocal))
                    permissionsLocal.add(m_permissionNodeView);
                // record the permissions in the cache for future reference
                m_permissionCache.set(p_keyLocal.getUserLocal().getUserId(), p_nodeLocal.getNodeId(), permissionsLocal);
                return permissionsLocal;
            }
            else
                throw new InvalidKeyException();
        }
        catch(NamingException e) {
            throw new EJBException(e);
        }
        catch(FinderException e) {
            throw new EJBException(e);
        }
    }

    /**
     * Check by searching up the tree for the set of persons which are assigned
     * to at least one of the specified roles in the given node.
     *
     * @param p_key User's security key
     * @param p_node the node for which the persons are required
     * @param p_transition the transition for the desired set of roles
     *
     * @ejb.interface-method view-type="local"
     **/
    public java.util.Collection getPersonsLocalAssignedToTransitionLocal(com.velocityme.interfaces.KeySessionLocal p_keyLocal,
                                                                         com.velocityme.interfaces.NodeLocal p_nodeLocal,
                                                                         com.velocityme.interfaces.TransitionLocal p_transitionLocal)
        throws InvalidKeyException {
        Collection results = new ArrayList();
        try {
            if (p_keyLocal.isValid()) {
                SearchLocal searchLocal = createSearchSpaceLocal(p_keyLocal, p_nodeLocal, new Boolean(false));
                results = PersonUtil.getLocalHome().findBySearchAndRoles(searchLocal, p_transitionLocal);
                results.addAll(PersonUtil.getLocalHome().findBySearchGroupAndRoles(searchLocal, p_transitionLocal));
                removeSearchSpaceLocal(p_keyLocal, searchLocal);
            }
            else
                throw new InvalidKeyException();
        }
        catch(NamingException e) {
            throw new EJBException(e);
        }
        catch(FinderException e) {
            throw new EJBException(e);
        }
        return results;
    }
    
    /**
     * Check by searching up the tree for the set of contactables which are assigned
     * to at least one of the specified roles in the given node.
     *
     * @param p_key User's security key
     * @param p_task the task for which the contactables are required
     * @param p_transition the transition for the desired set of roles
     *
     * @ejb.interface-method view-type="local"
     **/
    public java.util.Set getContactablesLocalAssignedToTransitionLocal(com.velocityme.interfaces.KeySessionLocal p_keyLocal,
                                                                       com.velocityme.interfaces.TaskLocal p_taskLocal,
                                                                       com.velocityme.interfaces.TransitionLocal p_transitionLocal)
        throws InvalidKeyException {
        Set contactablesLocal = new HashSet();
        // add owner if necessary
        if (p_transitionLocal.getIsOwnerResponsible().booleanValue())
            contactablesLocal.add(p_taskLocal.getOwnerContactableLocal());
        // add current user person if necessary
        if (p_transitionLocal.getIsUserResponsible().booleanValue())
            contactablesLocal.add(p_keyLocal.getUserLocal().getPersonLocal().getContactableLocal());
        // add all persons in roles
        Collection personsLocal = getPersonsLocalAssignedToTransitionLocal(p_keyLocal, p_taskLocal.getNodeLocal(), p_transitionLocal);
        Iterator i = personsLocal.iterator();
        while(i.hasNext()) {
            contactablesLocal.add(((PersonLocal)i.next()).getContactableLocal());
        }
        return contactablesLocal;
    }
    
    /**
     * Check by searching up the tree for the set of contactables which are assigned
     * to at least one of the specified roles in the given node.
     *
     * @param p_key User's security key
     * @param p_task the task for which the contactables are required
     * @param p_transition the transition for the desired set of roles
     *
     * @ejb.interface-method view-type="local"
     **/
    public java.util.Set getContactablesLocalAssignedToTaskStateMachineLocal(com.velocityme.interfaces.KeySessionLocal p_keyLocal,
                                                                       com.velocityme.interfaces.TaskLocal p_taskLocal,
                                                                       com.velocityme.interfaces.TaskStateMachineLocal p_taskStateMachineLocal)
        throws InvalidKeyException {
        Set contactablesLocal = new HashSet();
        // add all persons in roles
        try {
            if (p_keyLocal.isValid()) {
                SearchLocal searchLocal = createSearchSpaceLocal(p_keyLocal, p_taskLocal.getNodeLocal(), Boolean.FALSE);
                Collection personsLocal = PersonUtil.getLocalHome().findBySearchAndRoles(searchLocal, p_taskStateMachineLocal);
                personsLocal.addAll(PersonUtil.getLocalHome().findBySearchGroupAndRoles(searchLocal, p_taskStateMachineLocal));
                removeSearchSpaceLocal(p_keyLocal, searchLocal);
                Iterator i = personsLocal.iterator();
                while(i.hasNext()) {
                    contactablesLocal.add(((PersonLocal)i.next()).getContactableLocal());
                }
            }
            else
                throw new InvalidKeyException();
        }
        catch(NamingException e) {
            throw new EJBException(e);
        }
        catch(FinderException e) {
            throw new EJBException(e);
        }
        return contactablesLocal;
    }
    
    /**
     * Search the node tree for the tasks assigned to the specified person.
     *
     * @param p_key User's security key
     *
     * @ejb.interface-method view-type="local"
     **/
    public Collection findAllMyResponsibleTaskLocals(com.velocityme.interfaces.KeySessionLocal p_keyLocal)
        throws InvalidKeyException {
        try {
            if (p_keyLocal.isValid()) {
                Collection tasksLocal = TaskUtil.getLocalHome().findAllMyResponsibleTasks(p_keyLocal.getUserLocal().getPersonLocal().getContactableLocal());
                return tasksLocal;
            }
            else
                throw new InvalidKeyException();
        }
        catch(NamingException e) {
            throw new EJBException(e);
        }
        catch(FinderException e) {
            throw new EJBException(e);
        }
    }
    
    /**
     * Search the node tree for the tasks assigned to the specified person below
     * the specified node.
     *
     * @param p_key User's security key
     * @param p_node The node which is the root of the search
     *
     * @ejb.interface-method view-type="local"
     **/
    public Collection findMyResponsibleTaskLocals(com.velocityme.interfaces.KeySessionLocal p_keyLocal, 
                                                  com.velocityme.interfaces.NodeLocal p_nodeLocal)
        throws InvalidKeyException {
        try {
            if (p_keyLocal.isValid()) {
                SearchLocal searchLocal = createSearchSpaceLocal(p_keyLocal, p_nodeLocal, new Boolean(true));
                Collection tasksLocal = TaskUtil.getLocalHome().findMyResponsibleTasks(searchLocal, p_keyLocal.getUserLocal().getPersonLocal().getContactableLocal());
                removeSearchSpaceLocal(p_keyLocal, searchLocal);
                return tasksLocal;
            }
            else
                throw new InvalidKeyException();
        }
        catch(NamingException e) {
            throw new EJBException(e);
        }
        catch(FinderException e) {
            throw new EJBException(e);
        }
    }
    
    /**
     * Search the node tree for the tasks assigned any of the specified contactables.
     *
     * @param p_key User's security key
     * @param p_node The node which is the root of the search
     * @param p_contactables Collection of contactables to search for
     * @param p_includeDescendents Should the descendent contactables be included
     *
     * @ejb.interface-method view-type="local"
     **/
    public Collection findContactablesResponsibleTaskLocals(com.velocityme.interfaces.KeySessionLocal p_keyLocal, 
                                                            com.velocityme.interfaces.NodeLocal p_nodeLocal,
                                                            java.util.Collection p_contactablesLocal,
                                                            boolean p_includeDescendents)
        throws InvalidKeyException {
        try {
            if (p_keyLocal.isValid()) {
                SearchLocal searchLocal = createSearchSpaceLocal(p_keyLocal, p_nodeLocal, new Boolean(true));
                Collection tasksLocal = new LinkedList();

                Set allContactablesLocal = new HashSet();
                Iterator i = p_contactablesLocal.iterator();
                while(i.hasNext()) {
                    ContactableLocal contactableLocal = (ContactableLocal)i.next();
                    allContactablesLocal.add(contactableLocal);
                    if (p_includeDescendents) {
                        Set descendents = ContactableSessionUtil.getLocalHome().create().getNestedContactableSet(p_keyLocal, contactableLocal);
                        allContactablesLocal.addAll(descendents);
                    }
                }
                ContactablePK[] contactablePks = new ContactablePK[allContactablesLocal.size()];
                i = allContactablesLocal.iterator();
                int index = 0;
                while(i.hasNext()) {
                    ContactableLocal contactableLocal = (ContactableLocal)i.next();
                    contactablePks[index++] = new ContactablePK(contactableLocal.getContactableId());
                }
                
                Connection connection = null;
                Statement statement = null;
                try {
                    connection = DataSourceConnection.getConnection();
                    statement = connection.createStatement();
                    StringBuffer sql = new StringBuffer();
                    sql.append("SELECT DISTINCT task.taskId FROM task ");
                    // note that the column names are the wrong way around in searchlevelnode
                    sql.append("INNER JOIN searchlevelnode ON task.taskId=searchlevelnode.searchLevelId ");
                    sql.append("INNER JOIN searchlevel ON searchlevelnode.nodeId=searchlevel.searchLevelId ");
                    sql.append("INNER JOIN node ON task.nodeIdFk=node.nodeId ");
//                    sql.append("INNER JOIN changedelta ON node.nodeId=changedelta.dBObjectIdFk ");
//                    sql.append("INNER JOIN changedeltaitem ON changedelta.changeDeltaId=changedeltaitem.changeDeltaIdFk ");
//                    sql.append("INNER JOIN changedeltatype ON changedelta.changeDeltaTypeIdFk=changedeltatype.changeDeltaTypeId ");
                    // only include tasks that have a responsible contactable in this case
                    // note that the column names are the wrong way around in responsiblecontactables
                    sql.append("INNER JOIN responsiblecontactables ON task.taskId=responsiblecontactables.contactableId ");
                    StringBuffer where = new StringBuffer();
                    where.append(composeSearchQuery(searchLocal));
                    if (where.length() > 0)
                        where.append(" AND ");
                    where.append(composeContactableQuery(contactablePks));
                    sql.append(" WHERE ");
                    sql.append(where);
                    
                    ResultSet resultSet = statement.executeQuery(sql.toString());
                    int taskId = -1;
                    while(resultSet.next() ) {
                        taskId = resultSet.getInt(1);
                        tasksLocal.add(TaskUtil.getLocalHome().findByPrimaryKey(new TaskPK(new Integer(taskId))));
                    }
                }
                catch(SQLException e) {
                    throw new EJBException(e);
                }
                catch(NamingException e) {
                    throw new EJBException(e);
                }
                finally {
                    try {
                        if (statement != null) statement.close();
                    }
                    catch(Exception e) {}
                    try {
                        if (connection != null) connection.close();
                    }
                    catch(Exception e) {}
                }
                removeSearchSpaceLocal(p_keyLocal, searchLocal);
                return tasksLocal;
            }
            else
                throw new InvalidKeyException();
        }
        catch(NamingException e) {
            throw new EJBException(e);
        }
        catch(FinderException e) {
            throw new EJBException(e);
        }
        catch(CreateException e) {
            throw new EJBException(e);
        }
    }
    
    /**
     * Search the node tree for the tasks assigned to the specified person.
     *
     * @param p_key User's security key
     *
     * @ejb.interface-method view-type="local"
     **/
    public Collection findAllMyInterestedTaskLocals(com.velocityme.interfaces.KeySessionLocal p_keyLocal)
        throws InvalidKeyException {
        try {
            if (p_keyLocal.isValid()) {
                Collection tasksLocal = TaskUtil.getLocalHome().findAllMyInterestedTasks(p_keyLocal.getUserLocal().getPersonLocal().getContactableLocal());
                return tasksLocal;
            }
            else
                throw new InvalidKeyException();
        }
        catch(NamingException e) {
            throw new EJBException(e);
        }
        catch(FinderException e) {
            throw new EJBException(e);
        }
    }
    
    /**
     * Search the node tree for the tasks assigned to the specified person below
     * the specified node.
     *
     * @param p_key User's security key
     * @param p_node The node which is the root of the search
     *
     * @ejb.interface-method view-type="local"
     **/
    public Collection findMyInterestedTaskLocals(com.velocityme.interfaces.KeySessionLocal p_keyLocal, 
                                                 com.velocityme.interfaces.NodeLocal p_nodeLocal)
        throws InvalidKeyException {
        try {
            if (p_keyLocal.isValid()) {
                SearchLocal searchLocal = createSearchSpaceLocal(p_keyLocal, p_nodeLocal, new Boolean(true));
                Collection tasksLocal = TaskUtil.getLocalHome().findMyInterestedTasks(searchLocal, p_keyLocal.getUserLocal().getPersonLocal().getContactableLocal());
                removeSearchSpaceLocal(p_keyLocal, searchLocal);
                return tasksLocal;
            }
            else
                throw new InvalidKeyException();
        }
        catch(NamingException e) {
            throw new EJBException(e);
        }
        catch(FinderException e) {
            throw new EJBException(e);
        }
    }
    
    /**
     * Search the node tree for the tasks not in the start state.
     *
     * @param p_key User's security key
     * @param p_node The node which is the root of the search
     *
     * @ejb.interface-method view-type="local"
     **/
    public Collection findActiveTaskLocals(com.velocityme.interfaces.KeySessionLocal p_keyLocal, 
                                           com.velocityme.interfaces.NodeLocal p_nodeLocal)
        throws InvalidKeyException {
        try {
            if (p_keyLocal.isValid()) {
                SearchLocal searchLocal = createSearchSpaceLocal(p_keyLocal, p_nodeLocal, new Boolean(true));
                Collection tasksLocal = TaskUtil.getLocalHome().findActiveTasks(searchLocal);
                removeSearchSpaceLocal(p_keyLocal, searchLocal);
                return tasksLocal;
            }
            else
                throw new InvalidKeyException();
        }
        catch(NamingException e) {
            throw new EJBException(e);
        }
        catch(FinderException e) {
            throw new EJBException(e);
        }
    }
    
    /**
     * Search the node tree for the tasks have been completed since a specified
     * date.
     *
     * @param p_key User's security key
     * @param p_node The node which is the root of the search
     * @param p_date The date from which to accept completed tasks
     *
     * @ejb.interface-method view-type="local"
     **/
    public Collection findCompletedTaskLocals(com.velocityme.interfaces.KeySessionLocal p_keyLocal, 
                                              com.velocityme.interfaces.NodeLocal p_nodeLocal,
                                              java.util.Date p_date)
        throws InvalidKeyException {
        try {
            if (p_keyLocal.isValid()) {
                SearchLocal searchLocal = createSearchSpaceLocal(p_keyLocal, p_nodeLocal, new Boolean(true));
                StateLocal startStateLocal = StateUtil.getLocalHome().findStartState();
                Collection tasksLocal = TaskUtil.getLocalHome().findTasksByState(searchLocal, startStateLocal);
                removeSearchSpaceLocal(p_keyLocal, searchLocal);
                // Note that the following implementation is very inefficient and ought to
                // be performed using either SQL or EJBQL.
                Iterator i = tasksLocal.iterator();
                Collection resultTasksLocal = new ArrayList();
                while(i.hasNext()) {
                    TaskLocal taskLocal = (TaskLocal)i.next();
                    Iterator t = taskLocal.getNodeLocal().getDBObjectLocal().getChangeDeltasLocal().iterator();
                    while(t.hasNext()) {
                        ChangeDeltaLocal changeDeltaLocal = (ChangeDeltaLocal)t.next();
                        if (changeDeltaLocal.getDate().after(p_date)) {
                            resultTasksLocal.add(taskLocal);
                            break;
                        }
                    }
                }
                return resultTasksLocal;
            }
            else
                throw new InvalidKeyException();
        }
        catch(NamingException e) {
            throw new EJBException(e);
        }
        catch(FinderException e) {
            throw new EJBException(e);
        }
    }
    
    /**
     * Search the node tree for the tasks have changed within the specified date
     * range.
     *
     * @param p_key User's security key
     * @param p_node The node which is the root of the search
     * @param p_startDate The date from which to accept changed tasks
     * @param p_stopDate The date up until which to accept changed tasks
     *
     * @ejb.interface-method view-type="local"
     **/
    public Collection findChangedTaskLocals(com.velocityme.interfaces.KeySessionLocal p_keyLocal, 
                                            com.velocityme.interfaces.NodeLocal p_nodeLocal,
                                            java.util.Date p_startDate,
                                            java.util.Date p_stopDate)
        throws InvalidKeyException {
        try {
            if (p_keyLocal.isValid()) {
                SearchLocal searchLocal = createSearchSpaceLocal(p_keyLocal, p_nodeLocal, new Boolean(true));
                Collection tasksLocal = new LinkedList();
                
                Connection connection = null;
                Statement statement = null;
                try
                {
                    connection = DataSourceConnection.getConnection();
                    statement = connection.createStatement();
                    StringBuffer sql = new StringBuffer();
                    sql.append("SELECT DISTINCT task.taskId FROM task ");
                    // note that the column names are the wrong way around in searchlevelnode
                    sql.append("INNER JOIN searchlevelnode ON task.taskId=searchlevelnode.searchLevelId ");
                    sql.append("INNER JOIN searchlevel ON searchlevelnode.nodeId=searchlevel.searchLevelId ");
                    sql.append("INNER JOIN node ON task.nodeIdFk=node.nodeId ");
                    sql.append("INNER JOIN changedelta ON node.nodeId=changedelta.dBObjectIdFk ");
                    sql.append("INNER JOIN changedeltaitem ON changedelta.changeDeltaId=changedeltaitem.changeDeltaIdFk ");
                    sql.append("INNER JOIN changedeltatype ON changedelta.changeDeltaTypeIdFk=changedeltatype.changeDeltaTypeId ");
                    StringBuffer where = new StringBuffer();
                    where.append(composeSearchQuery(searchLocal));
                    if (where.length() > 0)
                        where.append(" AND ");
                    where.append(composeChangeQuery(p_startDate, p_stopDate));
                    sql.append(" WHERE ");
                    sql.append(where);
                    
                    ResultSet resultSet = statement.executeQuery(sql.toString());
                    int taskId = -1;
                    while(resultSet.next() ) {
                        taskId = resultSet.getInt(1);
                        tasksLocal.add(TaskUtil.getLocalHome().findByPrimaryKey(new TaskPK(new Integer(taskId))));
                    }
                }
                catch(SQLException e) {
                    throw new EJBException(e);
                }
                catch(NamingException e) {
                    throw new EJBException(e);
                }
                finally {
                    try {
                        if (statement != null) statement.close();
                    }
                    catch(Exception e) {}
                    try {
                        if (connection != null) connection.close();
                    }
                    catch(Exception e) {}
                }
                removeSearchSpaceLocal(p_keyLocal, searchLocal);
                return tasksLocal;
            }
            else
                throw new InvalidKeyException();
        }
        catch(FinderException e) {
            throw new EJBException(e);
        }
    }
    
    /**
     * Convert a set of taskLocals to NodeSearchResults for display by a client
     *
     * @param p_keyLocal User's security key
     * @param p_tasksLocal The collection of tasks that have been found
     * @param p_rootPathName The path name of the root of the search
     *
     * @ejb.interface-method view-type="local"
     **/
    public Collection convertTasksToResults(Collection p_tasksLocal, String p_rootPathName) {
        Pattern pattern = Pattern.compile(p_rootPathName);
        Collection results = new ArrayList();
        Iterator i = p_tasksLocal.iterator();
        while (i.hasNext()) {
            TaskLocal taskLocal = (TaskLocal)i.next();
            Integer priority = taskLocal.getPriority();
            Byte percent = taskLocal.getPercentageComplete();
            String taskType;
            String status;
            if (taskLocal.getType().equals("Anchor")) {
                taskType = status = "";
                percent = new Byte((byte)0);
            }
            else {
                taskType = taskLocal.getTaskStateMachineLocal().getNodeLocal().getName();
                status = taskLocal.getStatusLocal().getNodeLocal().getName();
            }
            Collection contactables = taskLocal.getResponsibleContactablesLocal();
            StringBuffer strBuff = new StringBuffer();
            Iterator j = contactables.iterator();
            while (j.hasNext()) {
                ContactableLocal contactableLocal = (ContactableLocal)j.next();
                strBuff.append(contactableLocal.getNodeLocal().getName());
                if (j.hasNext())
                    strBuff.append(", ");
            }
            String responsible = strBuff.toString();
            Matcher matcher = pattern.matcher(taskLocal.getNodeLocal().getPathName());
            String pathName = matcher.replaceFirst(".");
            results.add(new NodeSearchResult(taskLocal.getTaskId(), priority, percent, pathName, taskType, status, responsible));
        }
        return results;
    }
    
    private String composeSearchQuery(SearchLocal searchLocal) {
        StringBuffer query = new StringBuffer();
        query.append("searchlevel.searchIdFk=");
        query.append(searchLocal.getSearchId().toString());
        return query.toString();
    }
    
    private String composeIdQuery(Integer[] p_ids) {
        StringBuffer query = new StringBuffer();
        if (p_ids.length > 0) {
            query.append("(");
            for (int i = 0; i < p_ids.length; i++) {
                if (i != 0)
                    query.append(" OR ");
                query.append("task.taskId=");
                query.append(p_ids[i].toString());
            }
            query.append(")");
        }
        return query.toString();
    }
    
    private String composeKeywordQuery(String[] p_keywords) {
        StringBuffer query = new StringBuffer();
        if (p_keywords.length > 0) {
            query.append("(");
            for (int i = 0; i < p_keywords.length; i++) {
                String uppercase = p_keywords[i].toUpperCase();
                if (i != 0)
                    query.append(" OR ");
                query.append("(UPPER(node.name) LIKE '%");
                query.append(uppercase);
                query.append("%' OR UPPER(node.description) LIKE '%");
                query.append(uppercase);
                query.append("%' OR UPPER(changedeltaitem.description) LIKE '%");
                query.append(uppercase);
                query.append("%')");
            }
            query.append(")");
        }
        return query.toString();
    }
    
    private String composeChangeQuery(java.util.Date p_start, java.util.Date p_stop) {
        StringBuffer query = new StringBuffer();
        if (p_start != null || p_stop != null) {
            query.append("(");
            if (p_start != null) {
                query.append("changedelta.date>'");
                query.append(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(p_start));
                query.append("'");
                if (p_stop != null)
                    query.append(" AND ");
            }
            if (p_stop != null) {
                query.append("changedelta.date<'");
                query.append(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(p_stop));
                query.append("'");
            }
            query.append(")");
        }
        return query.toString();
    }
    
    private String composeDateQuery(java.util.Date p_start, java.util.Date p_stop) {
        StringBuffer query = new StringBuffer();
        if (p_start != null || p_stop != null) {
            query.append("(changedeltatype.name='Create'");
            if (p_start != null) {
                query.append(" AND changedelta.date>'");
                query.append(new SimpleDateFormat("yyyy-MM-dd").format(p_start));
                query.append("'");
            }
            if (p_stop != null) {
                query.append(" AND changedelta.date<'");
                query.append(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(p_stop));
                query.append("'");
            }
            query.append(")");
        }
        return query.toString();
    }
    
    private String composeStatusQuery(StatusPK[] p_statusPks) {
        StringBuffer query = new StringBuffer();
        if (p_statusPks.length > 0) {
            query.append("(");
            for (int i = 0; i < p_statusPks.length; i++) {
                if (i != 0)
                    query.append(" OR ");
                query.append("task.statusIdFk=");
                query.append(p_statusPks[i].getStatusId().toString());
            }
            query.append(")");
        }
        return query.toString();
    }
    
    private String composeContactableQuery(ContactablePK[] p_contactablePks) {
        StringBuffer query = new StringBuffer();
        if (p_contactablePks.length > 0) {
            query.append("(");
            for (int i = 0; i < p_contactablePks.length; i++) {
                if (i != 0)
                    query.append(" OR ");
                query.append("(responsiblecontactables.taskId=");
                query.append(p_contactablePks[i].getContactableId().toString());
                query.append(")");
            }
            query.append(")");
        }
        return query.toString();
    }
    
    /**
     * Search the node tree for the tasks which meet the specified search parameters
     *
     * @param p_key User's security key
     * @param p_node The node which is the root of the search
     *
     * @ejb.interface-method view-type="local"
     **/
    public Collection findParameterSearch(com.velocityme.interfaces.KeySessionLocal p_keyLocal, 
                                          com.velocityme.interfaces.NodeLocal p_nodeLocal,
                                          java.lang.Integer[] p_ids, 
                                          java.lang.String[] p_keywords,
                                          java.util.Date p_start,
                                          java.util.Date p_stop,
                                          com.velocityme.interfaces.StatusPK[] p_statusPks,
                                          com.velocityme.interfaces.ContactablePK[] p_contactablePks)
        throws InvalidKeyException {
        try {
            if (p_keyLocal.isValid()) {
                SearchLocal searchLocal = createSearchSpaceLocal(p_keyLocal, p_nodeLocal, new Boolean(true));
                Collection tasksLocal = new LinkedList();
                
                Connection connection = null;
                Statement statement = null;
                try
                {
                    connection = DataSourceConnection.getConnection();
                    statement = connection.createStatement();
                    StringBuffer sql = new StringBuffer();
                    sql.append("SELECT DISTINCT task.taskId FROM task ");
                    // note that the column names are the wrong way around in searchlevelnode
                    sql.append("INNER JOIN searchlevelnode ON task.taskId=searchlevelnode.searchLevelId ");
                    sql.append("INNER JOIN searchlevel ON searchlevelnode.nodeId=searchlevel.searchLevelId ");
                    sql.append("INNER JOIN node ON task.nodeIdFk=node.nodeId ");
                    sql.append("INNER JOIN changedelta ON node.nodeId=changedelta.dBObjectIdFk ");
                    sql.append("INNER JOIN changedeltaitem ON changedelta.changeDeltaId=changedeltaitem.changeDeltaIdFk ");
                    sql.append("INNER JOIN changedeltatype ON changedelta.changeDeltaTypeIdFk=changedeltatype.changeDeltaTypeId ");
                    StringBuffer where = new StringBuffer();
                    where.append(composeSearchQuery(searchLocal));
                    if (p_ids.length > 0) {
                        if (where.length() > 0) where.append(" AND ");
                        where.append(composeIdQuery(p_ids));
                    }
                    if (p_keywords.length > 0) {
                        if (where.length() > 0) where.append(" AND ");
                        where.append(composeKeywordQuery(p_keywords));
                    }
                    if (p_start != null || p_stop != null) {
                        if (where.length() > 0) where.append(" AND ");
                        where.append(composeDateQuery(p_start, p_stop));
                    }
                    if (p_statusPks.length > 0) {
                        if (where.length() > 0) where.append(" AND ");
                        where.append(composeStatusQuery(p_statusPks));
                    }
                    if (p_contactablePks.length > 0) {
                        if (where.length() > 0) where.append(" AND ");
                        where.append(composeContactableQuery(p_contactablePks));
                        // only include tasks that have a responsible contactable in this case
                        // note that the column names are the wrong way around in responsiblecontactables
                        sql.append("INNER JOIN responsiblecontactables ON task.taskId=responsiblecontactables.contactableId ");
                    }
                    sql.append(" WHERE ");
                    sql.append(where);
                    
                    System.out.println(sql.toString());
                    ResultSet resultSet = statement.executeQuery(sql.toString());
                    int taskId = -1;
                    while(resultSet.next() ) {
                        taskId = resultSet.getInt(1);
                        tasksLocal.add(TaskUtil.getLocalHome().findByPrimaryKey(new TaskPK(new Integer(taskId))));
                    }
                }
                catch(SQLException e) {
                    throw new EJBException(e);
                }
                catch(NamingException e) {
                    throw new EJBException(e);
                }
                finally {
                    try {
                        if (statement != null) statement.close();
                    }
                    catch(Exception e) {}
                    try {
                        if (connection != null) connection.close();
                    }
                    catch(Exception e) {}
                }
                removeSearchSpaceLocal(p_keyLocal, searchLocal);
                return convertTasksToResults(tasksLocal, p_nodeLocal.getPathName());
            }
            else
                throw new InvalidKeyException();
        }
        catch(FinderException e) {
            throw new EJBException(e);
        }
    }
    
    private void addChangeDeltaAttributesToItem(Element el, ChangeDeltaLocal cdLocal, DateFormat dateFormatter) {
        el.setAttribute("type", cdLocal.getChangeDeltaTypeLocal().getName());
        el.setAttribute("user", cdLocal.getUserLocal().getUserName());
        el.setAttribute("person", cdLocal.getUserLocal().getPersonLocal().getContactableLocal().getNodeLocal().getName());
        el.setAttribute("date", dateFormatter.format(cdLocal.getDate()));        
    }
    
    private void addItemElement(Document document, Element el, String name, String value) {
        Element nel = document.createElement("Item");
        nel.setAttribute("type", name);
        nel.appendChild(document.createTextNode(value));
        el.appendChild(nel);
    }
    
    /**
     * Get an XML string describing the report for the collection of nodes that are
     * provided.
     *
     * @param p_key User's security key
     * @param p_rootNodeLocal The root node of the search
     * @param p_results Collection of NodeResults that the report is required for
     * @param p_bStateChanges Should state changes be included in the report?
     *
     * @ejb.interface-method view-type="local"
     **/
    public java.lang.String searchReportXML(com.velocityme.interfaces.KeySessionLocal p_keyLocal,
                                            com.velocityme.interfaces.NodeLocal p_rootNodeLocal,
                                            java.util.Collection p_nodeSearchResults,
                                            boolean p_bStateChanges,
                                            boolean p_bComments) {
        String xmlString = new String();
        String nodePathName = p_rootNodeLocal.getPathName();
        Pattern pattern = Pattern.compile(nodePathName);
        try {
            DocumentBuilder parser = DocumentBuilderFactory.newInstance().newDocumentBuilder();
            Document document = parser.newDocument();
            document.appendChild(document.createProcessingInstruction("xml-stylesheet", "type='text/xsl' href='searchReport.xsl'"));
            document.appendChild(document.createElement("SearchReport"));
            Element rootElement = document.getDocumentElement();
            Element entryElement = document.createElement("Entries");
            DateFormat dateTimeFormatter = new SimpleDateFormat("yyyy/MM/dd HH:mm");
            DateFormat dateFormatter = DateFormat.getDateInstance();
            Iterator te = p_nodeSearchResults.iterator();
            while(te.hasNext()) {
                Object object = te.next();
                if (object instanceof NodeSearchResult) {
                    int itemCount = 1;
                    NodeSearchResult searchResult = (NodeSearchResult)object;
                    Element teElement = document.createElement("Task");
                    entryElement.appendChild(teElement);
                    // now create the "Created" item with the current node values
                    Element createdElement = document.createElement("Create");
                    createdElement.setAttribute("id", searchResult.m_ID.toString());
                    NodeLocal nodeLocal = NodeUtil.getLocalHome().findByPrimaryKey(new NodePK(searchResult.m_ID));
                    //Collection created = ChangeDeltaUtil.getLocalHome().findByObjectAndAction(nodeLocal.getDBObjectLocal(), "Create");
                    Collection created = nodeLocal.getDBObjectLocal().ejbSelectChangeDeltaByType(nodeLocal.getDBObjectLocal(), m_cdHelper.getDeltaTypeLocal(ChangeDeltaTypeBean.CREATE));
                    if (created.size() > 0) {
                        Iterator i = created.iterator();
                        addChangeDeltaAttributesToItem(createdElement, (ChangeDeltaLocal)i.next(), dateTimeFormatter);
                    }
                    addItemElement(document, createdElement, "Name", pattern.matcher(nodeLocal.getPathName()).replaceFirst("."));
                    addItemElement(document, createdElement, "Description", nodeLocal.getDescription());
                    teElement.appendChild(createdElement);
                    if (p_bStateChanges == true || p_bComments == true) {
                        // get a collection of all the change deltas
                        Collection changesLocal = nodeLocal.getDBObjectLocal().getChangeDeltasLocal();
                        Iterator ce = changesLocal.iterator();
                        while(ce.hasNext()) {
                            ChangeDeltaLocal cdLocal = (ChangeDeltaLocal)ce.next();
                            Element changeElement = null;
                            if (p_bStateChanges == true && cdLocal.getChangeDeltaTypeLocal().getName().equals("Change State"))
                                changeElement = document.createElement("Change");
                            if (p_bComments == true && cdLocal.getChangeDeltaTypeLocal().getName().equals("Post Comment"))
                                changeElement = document.createElement("Comment");
                            if (changeElement != null) {
                                itemCount++;
                                addChangeDeltaAttributesToItem(changeElement, cdLocal, dateTimeFormatter);
                                Iterator ci = cdLocal.getChangeDeltaItemsLocal().iterator();
                                while(ci.hasNext()) {
                                    ChangeDeltaItemLocal cdItemLocal = (ChangeDeltaItemLocal)ci.next();
                                    addItemElement(document, changeElement, cdItemLocal.getChangeDeltaItemTypeLocal().getName(), cdItemLocal.getDescription());
                                }
                                teElement.appendChild(changeElement);
                            }
                        }
                    }
                    createdElement.setAttribute("count", Integer.toString(itemCount));
                }
            }
            Element summaryElement = document.createElement("Summaries");
            summaryElement.setAttribute("node", nodePathName);
            summaryElement.setAttribute("date", dateFormatter.format(new java.util.Date()));
            summaryElement.setAttribute("count", Integer.toString(p_nodeSearchResults.size()));
            rootElement.appendChild(summaryElement);
            rootElement.appendChild(entryElement);
            Transformer transformer = TransformerFactory.newInstance().newTransformer();
            Source source = new DOMSource(document);
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            Result output = new StreamResult(outputStream);
            transformer.transform(source, output);
            xmlString = outputStream.toString();
        } catch(Exception e) {
            throw new EJBException(e);
        }
        return xmlString;
    }
    
    /**
     * Get a value object describing the report for the collection of nodes that are
     * provided.
     *
     * @param p_key User's security key
     * @param p_rootNodeLocal The root node of the search
     * @param p_results Collection of NodeResults that the report is required for
     * @param p_bStateChanges Should state changes be included in the report?
     *
     * @ejb.interface-method view-type="local"
     **/
    public java.util.Collection searchReportValue(com.velocityme.interfaces.KeySessionLocal p_keyLocal,
                                                  com.velocityme.interfaces.NodeLocal p_rootNodeLocal,
                                                  java.util.Collection p_nodeSearchResults,
                                                  boolean p_bStateChanges,
                                                  boolean p_bComments,
                                                  boolean p_bResponsibility) {
        Collection searchEntries = new ArrayList();
        String nodePathName = p_rootNodeLocal.getPathName();
        Pattern pattern = Pattern.compile(nodePathName);
        try {
            Iterator te = p_nodeSearchResults.iterator();
            while(te.hasNext()) {
                Object object = te.next();
                if (object instanceof NodeSearchResult) {
                    NodeSearchResult searchResult = (NodeSearchResult)object;
                    SearchEntry searchEntry = new SearchEntry(searchResult.m_ID, searchResult.m_priority);
                    NodeLocal nodeLocal = NodeUtil.getLocalHome().findByPrimaryKey(new NodePK(searchResult.m_ID));
                    Collection created = nodeLocal.getDBObjectLocal().ejbSelectChangeDeltaByType(nodeLocal.getDBObjectLocal(), m_cdHelper.getDeltaTypeLocal(ChangeDeltaTypeBean.CREATE));
                    if (created.size() > 0) {
                        Iterator i = created.iterator();
                        ChangeDeltaLocal changeDeltaLocal = (ChangeDeltaLocal)i.next();
                        SearchEntry.Transaction transaction = searchEntry.addTransaction(changeDeltaLocal.getChangeDeltaTypeLocal().getName(), 
                                                                                         changeDeltaLocal);
                        transaction.addRow("Name", pattern.matcher(nodeLocal.getPathName()).replaceFirst("."));
                        transaction.addRow("Description", nodeLocal.getDescription());
                    }
                    searchEntries.add(searchEntry);
                    if (p_bStateChanges == true || p_bComments == true || p_bResponsibility == true) {
                        // get a collection of all the change deltas
                        Collection changesLocal = nodeLocal.getDBObjectLocal().getChangeDeltasLocal();
                        Iterator ce = changesLocal.iterator();
                        while(ce.hasNext()) {
                            ChangeDeltaLocal cdLocal = (ChangeDeltaLocal)ce.next();
                            SearchEntry.Transaction transaction = null;
                            if (p_bStateChanges == true && cdLocal.getChangeDeltaTypeLocal().getName().equals("Change State"))
                                transaction = searchEntry.addTransaction("Change", cdLocal);
                            if (p_bComments == true && cdLocal.getChangeDeltaTypeLocal().getName().equals("Post Comment"))
                                transaction = searchEntry.addTransaction("Comment", cdLocal);
                            if (p_bResponsibility == true && cdLocal.getChangeDeltaTypeLocal().getName().equals("Reassign Responsibility"))
                                transaction = searchEntry.addTransaction("Reassign", cdLocal);
                            if (transaction != null) {
                                Iterator ci = cdLocal.getChangeDeltaItemsLocal().iterator();
                                while(ci.hasNext()) {
                                    ChangeDeltaItemLocal cdItemLocal = (ChangeDeltaItemLocal)ci.next();
                                    transaction.addRow(cdItemLocal.getChangeDeltaItemTypeLocal().getName(),
                                                       cdItemLocal.getDescription());
                                }
                            }
                        }
                    }
                }
            }
        } catch(Exception e) {
            throw new EJBException(e);
        }
        return searchEntries;
    }
    
    /**
     * Create the Session Bean.
     * @throws CreateException 
     */
    public void ejbCreate() throws CreateException {
        m_cdHelper = ChangeDeltaHelper.getInstance();
        m_permissionCache = PermissionCache.getInstance();
        try {
            m_directoryNodeLocal = NodeUtil.getLocalHome().findDirectoryNode();
            m_permissionNodeView = PermissionUtil.getLocalHome().findByPrimaryKey(new PermissionPK(new Integer(PermissionBean.NODE_VIEW)));
        }
        catch(Exception e) {
            throw new CreateException(e.getMessage());
        }
    }

    public void ejbActivate() throws java.rmi.RemoteException {
    }
    
    public void ejbPassivate() throws java.rmi.RemoteException {
    }
    
    public void ejbRemove() throws java.rmi.RemoteException {
    }
    
    public void setSessionContext(javax.ejb.SessionContext sessionContext) throws java.rmi.RemoteException {
        m_context = sessionContext;
    }
    
    
}
